# import paramiko
import chardet
import time
import re
from ..log import logger


def bwd_ssh_for_db(
        ssh_host, ssh_port, ssh_user, ssh_passwd, db_host, db_port, db_user, db_passwd, db_service_name, sql: str
) -> dict:
    if not sql.endswith(';'):
        sql += ';'
    client = paramiko.SSHClient()
    client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    client.connect(ssh_host, ssh_port, ssh_user, ssh_passwd, timeout=2)
    channel = client.invoke_shell()

    count_home = 0  # ssh根选项提示页
    # count_sql_return = 0  # 接收sql查询返回的次数
    count_exec_sql = 0  # 执行sql的次数
    setted_linesize = False
    data_tmp = []  # 多次接收的数据存到一起，接收完成后再处理
    log = []
    try:
        while getattr(client.get_transport(), 'is_active', None):
            data = channel.recv(4096)

            msg_detect = chardet.detect(data)
            # log.append(str(msg_detect) + '\n')
            msg_code = msg_detect['encoding']
            confidence = msg_detect['confidence']

            if confidence < 0.5:
                msg_code = 'gb2312'
            sdata = data.decode(msg_code)
            log.append(sdata)

            if '[4]非单点登录' in sdata:
                if count_home > 0:
                    break
                channel.send('4')
                count_home += 1
            elif '[2]数据库' in sdata:
                channel.send('2')
            elif '[2]service_name' in sdata:
                channel.send('2')
            elif 'sqlplus -l username -s service_name db [-p port]' in sdata:
                s = f'sqlplus -l {db_user} -s {db_service_name} {db_host} -p {db_port}\n'
                channel.send(s)
            elif '密码：' in sdata:
                channel.send(f'{db_passwd}\n')
            elif 'SQL>' in sdata and count_exec_sql == 0:
                if not setted_linesize:
                    setted_linesize = True
                    channel.send(f'set linesize 8000;\n')
                else:
                    count_exec_sql += 1
                    if not sql.endswith(';'):
                        sql += ';'
                    channel.send(f"{sql}\n")
                    # channel.send(f"select data_line_id from inf_c2c_order t where t.order_code = '23201912241547541035';\n")

            elif count_exec_sql == 1:  # 接收sql执行结果
                data_tmp += sdata
                if 'SQL>' in sdata:
                    # print('****************************** SQL执行结果 *****************************')
                    data_tmp_s = ''.join(data_tmp)
                    # print(data_tmp_s)
                    # print('****************************** SQL执行结果处理 *****************************')
                    s = re.sub(r'-[- ]*-', '', data_tmp_s)
                    s = re.sub('\t+', '\t', s).replace('\t ', '\t')
                    slist = s.split('\r\n')
                    slist = [s for s in slist if s != '' and 'SQL>' not in s]
                    description = slist.pop(-1) if 'rows selected' in slist[-1] else None
                    print({'results': slist[2:], 'description': description})
                    # print(''.join(log))
                    return {'values': slist[2:], 'description': description}
                # break
    except:
        logger.exception(''.join(log))
        raise


if __name__ == "__main__":
    ssh_host = ''
    ssh_port = 0
    ssh_user = ''
    ssh_passwd = ''
    db_host = ''
    db_port = '1521'
    db_user = ''
    db_passwd = ''
    db_service_name = ''
    bwd_ssh_for_db(
        ssh_host=ssh_host, ssh_port=ssh_port, ssh_user=ssh_user, ssh_passwd=ssh_passwd,
        db_host=db_host, db_port=db_port, db_user=db_user, db_passwd=db_passwd, db_service_name=db_service_name
    )

